package socratesteam.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import socratesteam.model.dominio.Persona;
import socratesteam.model.dominio.Tipo;
import socratesteam.model.excepcion.DAOExcepcion;
import socratesteam.model.util.ConexionBD;

public class PersonaDAO extends BaseDAO {
	
	private static final long serialVersionUID = 863458015076071938L;

	private Connection oConnection = null;
	private PreparedStatement oStatement = null;
	private ResultSet oSet = null;
	private String query = "";
	
	public void buscarPersona(Persona oPersona) throws DAOExcepcion {
		try {
			query = "SELECT P.C_DNI, P.C_NOMBRES, P.C_APELLIDO_PATERNO, P.C_APELLIDO_MATERNO, " +
					"P.N_SEXO, SE.C_NOMBRE_CORTO, SE.C_NOMBRE_LARGO, " +
					"P.N_TIPO_DOCUMENTO, TD.C_NOMBRE_CORTO AS C_TD_NOMBRE_CORTO, TD.C_NOMBRE_LARGO AS C_TD_NOMBRE_LARGO, " +
					"P.D_FECHA_NACIMIENTO " +
					"FROM TAB_PERSONA P " +
					"INNER JOIN TAB_TIPO SE ON SE.N_CODIGO = P.N_SEXO " +
					"INNER JOIN TAB_TIPO TD ON TD.N_CODIGO = P.N_TIPO_DOCUMENTO " +
					"WHERE P.C_DNI = " + oPersona.getDni();
			oConnection = ConexionBD.obtenerConexion();
			oStatement = oConnection.prepareStatement(query);
			oSet = oStatement.executeQuery();
			while (oSet.next()) {
				oPersona.setNombres(oSet.getString(2));
				oPersona.setApellidoPaterno(oSet.getString(3));
				oPersona.setApellidoMaterno(oSet.getString(4));
				oPersona.setSexo(new Tipo(oSet.getInt(5), oSet.getString(6), oSet.getString(7)));
				oPersona.setTipoDocumento(new Tipo(oSet.getInt(8), oSet.getString(9), oSet.getString(10)));
				oPersona.setFechaNacimiento(oSet.getDate(11));
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(oSet);
			this.cerrarStatement(oStatement);
			this.cerrarConexion(oConnection);
		}

	}
	
	
	/*  
	 * 
	 * */
	
	public Persona insertar(Persona oPersona) throws DAOExcepcion {
		String query = "insert into tab_Persona( c_dni, c_nombres, c_apellido_paterno, c_apellido_materno) values (?,?,?,?)";
		Connection oConnection = null;
		PreparedStatement oStatement = null;
		ResultSet oSet = null;
		try {
			oConnection = ConexionBD.obtenerConexion();
			oStatement = oConnection.prepareStatement(query);
			oStatement.setString(1, oPersona.getDni());
			oStatement.setString(2, oPersona.getNombres());
			oStatement.setString(3, oPersona.getApellidoPaterno());
			oStatement.setString(4, oPersona.getApellidoMaterno());
			int i = oStatement.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar registro");				
			}
			System.out.println("Registro OK");
			// Obtener el codigo del ultimo registro insertado
			query = "SELECT LAST_INSERT_ID()";
			oStatement = oConnection.prepareStatement(query);
			oSet = oStatement.executeQuery();
			if (oSet.next()) {
				oPersona.setDni(oSet.getString(1));
			}
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(oSet);
			this.cerrarStatement(oStatement);
			this.cerrarConexion(oConnection);
		}
		return oPersona;
	}

}
